#ifndef CONNECTION_H
#define CONNECTION_H

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDateTime>
#include <QString>
#include <QVariant>

static QString getDay()
{
	QString day;
	QDateTime current_date_time = QDateTime::currentDateTime();
	day = current_date_time.toString("yyyy-MM-dd");

	return day;
}

static QString getMonth()
{
	QString month;
	QDateTime current_date_time = QDateTime::currentDateTime();
	month = current_date_time.toString("yyyy-MM");

	return month;
}

static QString getStartTime()
{
	QString time;
	QString mounth = getMonth();
	QString day = getDay();
	// 使用QSqlQuery查询连接整张表，先要使用连接名获取该连接
	QSqlDatabase db = QSqlDatabase::database("database.db");
	QSqlQuery query(db);
	query.exec(QString("select start from '%1' where day = '%2'")
		.arg(mounth)
		.arg(day));
	while (query.next())
	{
		time = query.value(0).toString();
	}
	return time;
}

static QString getStopTime()
{
	QString time;
	QString mounth = getMonth();
	QString day = getDay();
	// 使用QSqlQuery查询连接整张表，先要使用连接名获取该连接
	QSqlDatabase db = QSqlDatabase::database("database.db");
	QSqlQuery query(db);
	query.exec(QString("select stop from '%1' where day = '%2'")
		.arg(mounth)
		.arg(day));
	while (query.next())
	{
		time = query.value(0).toString();
	}
	return time;
}

static void setStartTime()
{
	QString startTime = getStartTime();
	if (startTime.isEmpty() || startTime.isNull())
	{
		QString mounth = getMonth();
		QString day = getDay();
		// 使用QSqlQuery查询连接整张表，先要使用连接名获取该连接
		QSqlDatabase db = QSqlDatabase::database("database.db");
		QSqlQuery query(db);

		query.exec(QString("insert into '%1' values( '%2', datetime('now', 'localtime'), datetime('now', 'localtime'))")
			.arg(mounth)
			.arg(day));
	}
}

static void setStopTime()
{
	// 使用QSqlQuery查询连接整张表，先要使用连接名获取该连接
	QSqlDatabase db = QSqlDatabase::database("database.db");
	QSqlQuery query(db);

	QString mounth = getMonth();
	QString day = getDay();
	QString stopTime = getStopTime();
	if (stopTime.isEmpty() || stopTime.isNull())
	{
		query.exec(QString("insert into '%1' values( '%2', datetime('now', 'localtime'), datetime('now', 'localtime'))")
			.arg(mounth)
			.arg(day));
	}
	else
	{
		query.exec(QString("update '%1' set stop = datetime('now', 'localtime') where day = '%2'")
			.arg(mounth)
			.arg(day));
	}
}

static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("database.db");

    if(!db.open()) return false;

	bool result = false;
    QSqlQuery query;
	result = query.exec(QString(
      "create table if not exists clock (Hour int, Minute int, Music QString, Message QString)"));

	QString month = getMonth();
	QString sql(QString("create table if not exists '%1' ("
		"day varchar not null primary key,"
		"start TIMESTAMP default(datetime('now', 'localtime')),"
		"stop TIMESTAMP default(datetime('now', 'localtime')))").arg(month));

	result = query.exec(sql);

    return true;
}

#endif // CONNECTION_H
